﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Transactions;
using System.Data.Common;
using System.Data.SqlClient;
using Microsoft.Data.Extensions;
using System.Data.Objects.DataClasses;


namespace EntServ.DataModel
{
	public class Procedures
	{

		#region private variables

		System.Data.Objects.ObjectContext _dbContext;

		#endregion

		/// <summary>
		/// Initializes a new instance of the Procedures class
		/// </summary>
		/// <param name="ConnectionString">The connection string to be used.</param>
		internal Procedures(System.Data.Objects.ObjectContext objectContext)
		{
			if (_dbContext == null)
			{
				_dbContext = objectContext;
			}
		}


        /* HOW TO GENERATE METHODS THAT CALL SQL STORED PROCS
         * According to the SQL DB Standards, only procs that return exactly one resultset should be called from a DB client (like this class).
         * To add a new SP wrapper function, you are required to figure out 
         */


        #region Table Roles

        ///// <summary>
        ///// Returns a list of Role entities that the user belongs to.
        ///// A user belongs to a role if
        ///// <list type="bullet">
        ///// <item>the user is directly attached to a role</item>
        ///// <item>the groups the user belongs to are attached to a role</item>
        ///// <item>the groups' child groups are attached to a role</item>
        ///// <item>the associated groups the user belongs to are attached to a role</item>
        ///// </list>
        ///// Regarding Groups, a user only belongs to a role if he is eiher a group owner or a group member.
        ///// </summary>
        ///// <param name="ApplicationName">Name of the application.</param>
        ///// <param name="UserName">Name of the user.</param>
        ///// <returns></returns>
        //public List<Role> Roles_SelectRolesByUser(string ApplicationName, string UserName)
        //{
        //    using (ESDBEntities _esdb = new ESDBEntities())
        //        return _esdb.Roles_SelectRolesByUser(ApplicationName, UserName).ToList();

        //    //List<SqlParameter> _params = new List<SqlParameter>();
        //    //_params.Add(new SqlParameter("@ApplicationName", ApplicationName));
        //    //_params.Add(new SqlParameter("@UserName", UserName));

        //    //SqlParameter _returnParameter = new SqlParameter("@ReturnValue", System.Data.SqlDbType.Int);
        //    //_returnParameter.Direction = System.Data.ParameterDirection.ReturnValue;

        //    //_params.Add(_returnParameter);

        //    //int success = -10;

        //    //return ExecuteProc<Role>("Core.p_Roles_SelectRolesByUser", _params.ToArray(), _returnParameter, out success);
        //}

        /// <summary>
        /// Returns true if a user belongs to the requested role.
        /// A user belongs to a role if
        /// <list type="bullet">
        /// 		<item>the user is directly attached to a role</item>
        /// 		<item>the groups the user belongs to are attached to a role</item>
        /// 		<item>the groups' child groups are attached to a role</item>
        /// 		<item>the associated groups the user belongs to are attached to a role</item>
        /// 	</list>
        /// Regarding Groups, a user only belongs to a role if he is eiher a group owner or a group member.
        /// </summary>
        /// <param name="ApplicationName">Name of the application.</param>
        /// <param name="RoleName">Name of the role.</param>
        /// <param name="UserName">Name of the user.</param>
        /// <returns></returns>
        public bool Roles_IsUserInRole(string ApplicationName, string RoleName, string UserName)
        {
            List<SqlParameter> _params = new List<SqlParameter>();
            _params.Add(new SqlParameter("@ApplicationName", ApplicationName));
            _params.Add(new SqlParameter("@RoleName", RoleName));
            _params.Add(new SqlParameter("@UserName", UserName));

            SqlParameter _returnParameter = new SqlParameter("@ReturnValue", System.Data.SqlDbType.Int);
            _returnParameter.Direction = System.Data.ParameterDirection.ReturnValue;

            _params.Add(_returnParameter);

            string _procName = "Core.p_" + "Roles_IsUserInRole";

            return ExecuteProc(_procName, _params.ToArray(), _returnParameter) > 0 ?
                true : false
                ;
        }

        #endregion

        #region Table Groups

        ///// <summary>
        ///// Returns a list of Group entities that the user belongs to - regardless of his group membership type (owner, member, contact).
        ///// </summary>
        ///// <param name="UserName">Name of the user.</param>
        ///// <returns></returns>
        //public List<Group> Groups_SelectGroupsByUser(string UserName)
        //{
        //    using (ESDBEntities _esdb = new ESDBEntities())
        //        return _esdb.Groups_SelectGroupsByUser(UserName).ToList();

        //    /*
        //    List<SqlParameter> _params = new List<SqlParameter>();
        //    _params.Add(new SqlParameter("@UserName", UserName));

        //    SqlParameter _returnParameter = new SqlParameter("@ReturnValue", System.Data.SqlDbType.Int);
        //    _returnParameter.Direction = System.Data.ParameterDirection.ReturnValue;

        //    _params.Add(_returnParameter);

        //    int success = -10;

        //    string _procName = "Core.p_" + "something";

        //    return ExecuteProc<Group>(_procName, _params.ToArray(), _returnParameter, out success);
        //    * */
        //}

        #endregion

        #region Table GroupAssociations

        ///// <summary>
        ///// Returns a list of groups that are associated to the requested Group(Name).
        ///// </summary>
        ///// <param name="GroupName">Name of the group.</param>
        ///// <returns></returns>
        //public List<GroupAssociation> GroupAssociations_SelectGroupAssociations(string GroupName)
        //{

        //    using (ESDBEntities _esdb = new ESDBEntities())
        //        return _esdb.GroupAssociations_SelectAssociatedGroups(GroupName).ToList<GroupAssociation>();
            
        //}

        #endregion

        #region Table Users

        /// <summary>
        /// Users_s the name of the get user by.
        /// </summary>
        /// <param name="UserName">Name of the user.</param>
        /// <returns></returns>
        public bool Users_DoesUserExists(string UserName)
        {
            List<SqlParameter> _params = new List<SqlParameter>();
            _params.Add(new SqlParameter("@UserName", UserName));

            SqlParameter _returnParameter = new SqlParameter("@ReturnValue", System.Data.SqlDbType.Int);
            _returnParameter.Direction = System.Data.ParameterDirection.ReturnValue;

            _params.Add(_returnParameter);

            string _procName = "Core.p_" + "Users_UserExists";

            return ExecuteProc(_procName, _params.ToArray(), _returnParameter) > 0 ? true : false;
        }

        #endregion


        /*
		/// <summary>
		/// Executes the p_UsersInRoles_AddUsersToRoles stored procedure inside the EnterpriseServices database.
		/// </summary>
		/// <param name="ApplicationName">Name of the application.</param>
		/// <param name="Users">The users.</param>
		/// <param name="Roles">The roles.</param>
		/// <param name="UtcTime">The UTC time.</param>
		/// <returns></returns>
		public UserInRole UsersInRoles_AddUsersToRoles(string ApplicationName, IEnumerable<string> Users, IEnumerable<string> Roles, DateTime UtcTime)
		{
			List<SqlParameter> _params = new List<SqlParameter>();
			_params.Add(new SqlParameter("@ApplicationName", ApplicationName));
			_params.Add(AddStructuredParameter("@UserNames", Users, Constants.OneColumnGenericType));
			_params.Add(AddStructuredParameter("@RoleNames", Roles, Constants.OneColumnGenericType));
			_params.Add(new SqlParameter("@CurrentTimeUtc", UtcTime));

			// if the SP provides a return value, use the following code.
			// Else, use this: ExecuteProc(_sqlCommand, null)
			// Consider changing the return value of this function (UsersInRoles_AddUsersToRolesEx) to void

			SqlParameter _returnParameter = new SqlParameter("@ReturnValue", System.Data.SqlDbType.Int);
			_returnParameter.Direction = System.Data.ParameterDirection.ReturnValue;

			_params.Add(_returnParameter);

			int? _success;
			UserInRole _result = ExecuteProc<UserInRole>("Core.p_UsersInRoles_AddUsersToRoles", _params.ToArray(), out _success);

			if (_success != null)
			{
				throw new InvalidOperationException("The procedure did not provide a return value.");
			}

			return _result;
		}

		/// <summary>
		/// Executes the p_UsersInRoles_AddUsersToRolesEx stored procedure inside the EnterpriseServices database.
		/// </summary>
		/// <param name="ApplicationName">Name of the application.</param>
		/// <param name="Users">The users.</param>
		/// <param name="Roles">The roles.</param>
		/// <returns></returns>
		public int UsersInRoles_RemoveUsersFromRoles(string ApplicationName, IEnumerable<string> Users, IEnumerable<string> Roles)
		{
			SqlCommand _sqlCommand = new SqlCommand("Core.p_UsersInRoles_RemoveUsersFromRoles");
			_sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;

			_sqlCommand.Parameters.AddWithValue("@ApplicationName", ApplicationName);
			_sqlCommand.Parameters.Add(AddStructuredParameter("@UserNames", Users, Constants.OneColumnGenericType));
			_sqlCommand.Parameters.Add(AddStructuredParameter("@RoleNames", Roles, Constants.OneColumnGenericType));

			// if the SP provides a return value, use the following code.
			// Else, use this: ExecuteProc(_sqlCommand, null)
			// Consider changing the return value of this function (UsersInRoles_RemoveUsersFromRoles) to void

			SqlParameter _returnParameter = _sqlCommand.Parameters.Add("@ReturnValue", System.Data.SqlDbType.Int);
			_returnParameter.Direction = System.Data.ParameterDirection.ReturnValue;

			int? _result = ExecuteProc(_sqlCommand, _returnParameter);

			if (!_result.HasValue)
			{
				throw new InvalidOperationException("The procedure did not provide a return value.");
			}

			return _result.Value;
		}

		/// <summary>
		/// Executes the p_UsersInRoles_AddUsersToRoles stored procedure inside the EnterpriseServices database.
		/// </summary>
		/// <param name="ApplicationName">Name of the application.</param>
		/// <param name="Users">The users.</param>
		/// <param name="Roles">The roles.</param>
		/// <param name="UtcTime">The UTC time.</param>
		/// <returns></returns>
		public UserInRole Groups_SelectParentGroups(string ChildGroupName, bool DirectParentOnly)
		{
			List<SqlParameter> _params = new List<SqlParameter>();
			_params.Add(new SqlParameter("@GroupName", ChildGroupName));
			_params.Add(new SqlParameter("@DirectParentsOnly", DirectParentOnly));

			// if the SP provides a return value, use the following code.
			// Else, use this: ExecuteProc(_sqlCommand, null)
			// Consider changing the return value of this function (UsersInRoles_AddUsersToRolesEx) to void

			SqlParameter _returnParameter = new SqlParameter("@ReturnValue", System.Data.SqlDbType.Int);
			_returnParameter.Direction = System.Data.ParameterDirection.ReturnValue;

			_params.Add(_returnParameter);

			int? _success;
			UserInRole _result = ExecuteProc<UserInRole>("Core.p_Groups_SelectParentGroups", _params.ToArray(), out _success);

			if (_success != null)
			{
				throw new InvalidOperationException("The procedure did not provide a return value.");
			}

			return _result;
		}
        */




        /// <summary>
        /// Provides an easy way to build up a structured stored procedure parameter needed by
        /// SP's that use the SQL Server 2008 or later user defined table type.
        /// </summary>
        /// <param name="ParameterName">The name of the parameter.</param>
        /// <param name="Value">The value to use as an input for the stored procedure.</param>
        /// <param name="TypeName">The name of the SQL Server user defined table type.</param>
        /// <returns></returns>
        private SqlParameter AddStructuredParameter(string ParameterName, IEnumerable<string> Value, string TypeName)
        {
            // Although the MSDN documentation states that it's possible to pass a IEnumerable as a value
            // to a SqlParameter, it does not work. However, passing a datatable as the value parameter
            // to a SqlParameter works just fine.
            System.Data.DataTable dt = new System.Data.DataTable();
            dt.Columns.Add("myValue");
            foreach (string item in Value)
                dt.Rows.Add(new string[] { item });

            var _param = new SqlParameter(ParameterName, dt);
            _param.SqlDbType = System.Data.SqlDbType.Structured;
            _param.TypeName = TypeName;
            return _param;
        }

        /// <summary>
        /// Executes a stored procedure which has been set up in the command parameter including
        /// all the required parameters.
        /// <list type="bullet">
        /// List description here
        /// <item>rule 1</item>
        /// 		<item>rule 2</item>
        /// 	</list>
        /// </summary>
        /// <typeparam name="TEntity">The type of the entity.</typeparam>
        /// <param name="StoredProcedureName">Name of the stored procedure.</param>
        /// <param name="sqlParameter">The SQL parameter.</param>
        /// <param name="sqlOutputParameter">The SQL output parameter.</param>
        /// <param name="returnValue">The return value.</param>
        /// <returns>
        /// If the parameter ReturnParameter is null, null will be returned.
        /// Else the return value of the executed stored procedure will be returned.
        /// </returns>
        private List<TEntity> ExecuteProc<TEntity>(
            string StoredProcedureName,
            SqlParameter[] sqlParameter,
            SqlParameter sqlOutputParameter,
            out int returnValue)
        {
            List<TEntity> _resultset = new List<TEntity>();
            returnValue = -10;

            var command = _dbContext.CreateStoreCommand(StoredProcedureName, System.Data.CommandType.StoredProcedure, sqlParameter);

            try
            {
                using (command.Connection.CreateConnectionScope())
                using (TransactionScope _scope = new TransactionScope())
                {
                    _resultset = command.Materialize<TEntity>().ToList<TEntity>();

                    returnValue = (int)command.Parameters[sqlOutputParameter.ParameterName].Value;

                    _scope.Complete();
                }
            }
            catch (SqlException)
            {
                returnValue = -10;
            }

            return _resultset;
        }

        /// <summary>
        /// Executes a stored procedure which has been set up in the command parameter including
        /// all the required parameters.
        /// </summary>
        /// <param name="StoredProcedureName">Name of the stored procedure.</param>
        /// <param name="sqlParameter">The SQL parameter.</param>
        /// <param name="sqlOutputParameter">The SQL output parameter.</param>
        /// <returns></returns>
        private int ExecuteProc(string StoredProcedureName, SqlParameter[] sqlParameter, SqlParameter sqlOutputParameter)
        {
            int returnValue = -10;

            var command = _dbContext.CreateStoreCommand(StoredProcedureName, System.Data.CommandType.StoredProcedure, sqlParameter);

            try
            {
                using (command.Connection.CreateConnectionScope())
                using (TransactionScope _scope = new TransactionScope())
                {
                    command.ExecuteNonQuery();
                    returnValue = (int)command.Parameters[sqlOutputParameter.ParameterName].Value;
                    _scope.Complete();
                }
            }
            catch (SqlException)
            {
                returnValue = -10;
            }

            return returnValue;
        }

    }
}
